import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class test {
    static String DB_URL="jdbc:mysql://localhost:3306/db2";
    static String USER="root";
    static String PASS="Root";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String JDBC_DRIVER="com.mysql.cj.jdbc.Driver";
        Class.forName(JDBC_DRIVER);
        select();
        add();
//        del();
        update();
    }

    private static void select() throws SQLException {
        Connection con = DriverManager.getConnection(DB_URL,USER, PASS);
        String sql = "select * from student";
        java.sql.PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
            System.out.print("SNo="+rs.getString("Sid"));
            System.out.print("SName="+rs.getString("SName"));
            System.out.println("Birth="+rs.getString("SSex"));
        }
    }

    public static void add() throws SQLException {
        String sql = "insert into student(Sid,Sname,Ssex) values (?,?,?)";
        Connection con = DriverManager.getConnection(DB_URL,USER, PASS);
        java.sql.PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,110);
        ps.setString(2,"李白");
        ps.setString(3,"男");
        int rs=ps.executeUpdate();
        if (rs != 1) {
            System.out.println("插入失败");
            return;
        }
        System.out.println("插入成功");
        ps.close();
        con.close();
    }

    public static void del() throws SQLException {
        String sql = "delete from student where Sid=?";
        Connection con = DriverManager.getConnection(DB_URL,USER, PASS);
        java.sql.PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,13);
        int rs=ps.executeUpdate();
        if (rs != 1) {
            System.out.println("插入失败");
            return;
        }
        System.out.println("删除成功");
        ps.close();
        con.close();
    }
    public static void update() throws SQLException {
        String sql = "update student set Sname=? where Sid=? ";
        Connection con = DriverManager.getConnection(DB_URL,USER, PASS);
        java.sql.PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,"王阳明");
        ps.setInt(2,110);
        int rs=ps.executeUpdate();
        if (rs != 1) {
            System.out.println("插入失败");
            return;
        }
        System.out.println("修改成功");
        ps.close();
        con.close();
    }
}
